package DB.Connection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import commons.Constants;
import commons.Entities.Comment;
import commons.Entities.LegenCharacter;
import commons.Entities.LegenCharacterThin;
import commons.Entities.LegenDate;
import commons.Entities.Review;
import commons.Entities.SearchResults;
import commons.Entities.User;

public class DBConnectionManager {

	private static final Log logger = LogFactory.getLog(DBConnectionManager.class);

	private final static String username = Constants.DB_USERNAME;
	private final static String password = Constants.DB_PASSWORD;
	private final static String url = Constants.DB_URL;

	private final static DBConnectionManager INSTANCE = new DBConnectionManager();

	public static DBConnectionManager get() {
		return INSTANCE;
	}

	private DBConnectionManager() {
		DBConnectionPool.SetConnectionParams(url, username, password);
	}

	/**
	 * opens the connection
	 * 
	 * @return a connection
	 */
	public Connection openConn() {
		Connection newConn;
		do {
			newConn = DBConnectionPool.getConnection();
		} while (newConn == null);
		return newConn;
	}

	/**
	 * closes the connection
	 * 
	 * @param conn
	 */
	public void closeConn(Connection conn) {
		DBConnectionPool.endConnection(conn);
	}

	// All functions to select, insert and update the DB

	/**
	 * add a batch of characters to the characters table
	 * 
	 * @param lcList
	 *            list of characters to insert
	 */
	public void insertToCharacterTableInBatch(List<LegenCharacter> lcList) {

		Integer characterID;
		PreparedStatement pstmtInsert, pstmtUpdate, pstmtReview;
		Connection conn = openConn();

		// create and update full statement
		try {
			pstmtInsert = conn.prepareStatement(prepareStatmentForCharacterInsert(), Statement.RETURN_GENERATED_KEYS);
			pstmtUpdate = conn.prepareStatement(prepareStatmentForCharacterUpdate(), Statement.RETURN_GENERATED_KEYS);

			for (LegenCharacter lc : lcList) {

				characterID = checkIfCharacterExists(lc.getCharacterFBID());
				if (characterID != null) {
					// append all character's values
					pstmtUpdate.setString(1, lc.getCharacterName());
					pstmtUpdate.setString(2, lc.getCharacterFBID());
					pstmtUpdate.setString(3, lc.getBasedOn());
					pstmtUpdate.setString(4, lc.getPlaceOfBirth());
					pstmtUpdate.setString(5, lc.getGender());
					pstmtUpdate.setString(6, lc.getSpecies());
					pstmtUpdate.setString(7, lc.getParents());
					pstmtUpdate.setString(8, lc.getOccupation());
					pstmtUpdate.setString(9, lc.getRank());
					pstmtUpdate.setString(10, lc.getOrganizations());
					pstmtUpdate.setString(11, lc.getPowersOrAbilities());
					pstmtUpdate.setString(12, lc.getAppearsInTheseFictionalUniverses());
					pstmtUpdate.setString(13, lc.getChildren());
					pstmtUpdate.setString(14, lc.getCharacterCreatedBy());
					pstmtUpdate.setString(15, lc.getQuotations());
					pstmtUpdate.setString(16, lc.getEthnicity());
					pstmtUpdate.setString(17, lc.getEducation());
					pstmtUpdate.setString(18, lc.getHasPossessed());
					pstmtUpdate.setString(19, lc.getMedicalConditions());
					pstmtUpdate.setString(20, lc.getDateOfBirth());
					pstmtUpdate.setFloat(21, lc.getHeight());
					pstmtUpdate.setFloat(22, lc.getWeight());
					pstmtUpdate.setString(23, lc.getPlacesLived());
					pstmtUpdate.setInt(24, characterID);

					pstmtUpdate.addBatch();
				} else {
					// append all character's values
					pstmtInsert.setString(1, lc.getCharacterName());
					pstmtInsert.setString(2, lc.getCharacterFBID());
					pstmtInsert.setString(3, lc.getBasedOn());
					pstmtInsert.setString(4, lc.getPlaceOfBirth());
					pstmtInsert.setString(5, lc.getGender());
					pstmtInsert.setString(6, lc.getSpecies());
					pstmtInsert.setString(7, lc.getParents());
					pstmtInsert.setString(8, lc.getOccupation());
					pstmtInsert.setString(9, lc.getRank());
					pstmtInsert.setString(10, lc.getOrganizations());
					pstmtInsert.setString(11, lc.getPowersOrAbilities());
					pstmtInsert.setString(12, lc.getAppearsInTheseFictionalUniverses());
					pstmtInsert.setString(13, lc.getChildren());
					pstmtInsert.setString(14, lc.getCharacterCreatedBy());
					pstmtInsert.setString(15, lc.getQuotations());
					pstmtInsert.setString(16, lc.getEthnicity());
					pstmtInsert.setString(17, lc.getEducation());
					pstmtInsert.setString(18, lc.getHasPossessed());
					pstmtInsert.setString(19, lc.getMedicalConditions());
					pstmtInsert.setString(20, lc.getDateOfBirth());
					pstmtInsert.setFloat(21, lc.getHeight());
					pstmtInsert.setFloat(22, lc.getWeight());
					pstmtInsert.setString(23, lc.getPlacesLived());

					pstmtInsert.addBatch();
				}
			}
			pstmtUpdate.executeBatch();
			pstmtUpdate.close();

			pstmtInsert.executeBatch();
			ResultSet rs = pstmtInsert.getGeneratedKeys();

			// generate initial default reviews for all new characters
			pstmtReview = conn.prepareStatement(prepareStatmentForCharacterReview(), Statement.RETURN_GENERATED_KEYS);
			while (rs.next() != false) {
				// append all review's values
				pstmtReview.setInt(1, rs.getInt(1));
				for (int i = 2; i < 22; i++)
					pstmtReview.setInt(i, 5);
				pstmtReview.addBatch();
			}

			pstmtInsert.close();
			pstmtReview.executeBatch();
			pstmtReview.close();

		} catch (Exception e) {
			logger.error("Error while trying to insert to character table in batch.", e);
		} finally {
			closeConn(conn);
		}
	}

	/**
	 * checks if character already exists in characters table
	 * 
	 * @param characterFBID
	 * @return res TURE if exists and FLASE otherwise
	 */
	private Integer checkIfCharacterExists(String characterFBID) {

		ResultSet rs;
		Statement stmt;
		Integer res = null;
		Connection conn = openConn();

		try {
			stmt = conn.createStatement();

			rs = stmt.executeQuery("SELECT character_id FROM " + TableType.CHARACTERS.getStrRep() + " WHERE free_base_id = '" + characterFBID + "'");
			if (rs.next() != false)
				res = rs.getInt(1);
			stmt.close();
		} catch (SQLException e) {
			logger.error("Error while trying to check if character exists", e);
		} finally {
			closeConn(conn);
		}
		return res;
	}

	/**
	 * adds a character to the characters table
	 * 
	 * @param lc
	 */
	public Integer insertToCharacterTable(LegenCharacter lc) {

		ResultSet rs;
		Integer id = null;
		PreparedStatement pstmt;

		Connection conn = openConn();

		// create and update full statement
		try {
			pstmt = conn.prepareStatement(prepareStatmentForCharacterInsert(), Statement.RETURN_GENERATED_KEYS);

			// append all character's values
			pstmt.setString(1, lc.getCharacterName());
			pstmt.setString(2, null);
			pstmt.setString(3, lc.getBasedOn().equals("") ? null : lc.getBasedOn());
			pstmt.setString(4, lc.getPlaceOfBirth().equals("") ? null : lc.getPlaceOfBirth());
			pstmt.setString(5, lc.getGender().equals("") ? null : lc.getGender());
			pstmt.setString(6, lc.getSpecies().equals("") ? null : lc.getSpecies());
			pstmt.setString(7, lc.getParents().equals("") ? null : lc.getParents());
			pstmt.setString(8, lc.getOccupation().equals("") ? null : lc.getOccupation());
			pstmt.setString(9, lc.getRank().equals("") ? null : lc.getRank());
			pstmt.setString(10, lc.getOrganizations().equals("") ? null : lc.getOrganizations());
			pstmt.setString(11, lc.getPowersOrAbilities().equals("") ? null : lc.getPowersOrAbilities());
			pstmt.setString(12, lc.getAppearsInTheseFictionalUniverses().equals("") ? null : lc.getAppearsInTheseFictionalUniverses());
			pstmt.setString(13, lc.getChildren().equals("") ? null : lc.getChildren());
			pstmt.setString(14, lc.getCharacterCreatedBy().equals("") ? null : lc.getCharacterCreatedBy());
			pstmt.setString(15, lc.getQuotations().equals("") ? null : lc.getQuotations());
			pstmt.setString(16, lc.getEthnicity().equals("") ? null : lc.getEthnicity());
			pstmt.setString(17, lc.getEducation().equals("") ? null : lc.getEducation());
			pstmt.setString(18, lc.getHasPossessed().equals("") ? null : lc.getHasPossessed());
			pstmt.setString(19, lc.getMedicalConditions().equals("") ? null : lc.getMedicalConditions());
			pstmt.setString(20, lc.getDateOfBirth().equals("") ? null : lc.getDateOfBirth());
			pstmt.setFloat(21, lc.getHeight());
			pstmt.setFloat(22, lc.getWeight());
			pstmt.setString(23, lc.getPlacesLived().equals("") ? null : lc.getPlacesLived());

			pstmt.executeUpdate();
			rs = pstmt.getGeneratedKeys();
			rs.next();
			id = rs.getInt(1);
			insertNewCharacterReview(new Review(id));
			pstmt.close();
		} catch (Exception e) {
			logger.error("Error while trying to insert to character table.", e);
		} finally {
			closeConn(conn);
		}
		return id;
	}

	/**
	 * prepare a base character insert statement
	 * 
	 * @return a string containing INSERT to all the character attributes with
	 *         the VALUE ?
	 */
	private String prepareStatmentForCharacterInsert() {

		StringBuffer attrs = new StringBuffer();
		StringBuffer vals = new StringBuffer();

		attrs = new StringBuffer();

		attrs.append("character_name,");
		attrs.append("free_base_id,");
		attrs.append("based_on,");
		attrs.append("place_of_birth,");
		attrs.append("gender,");
		attrs.append("species,");
		attrs.append("parents,");
		attrs.append("occupation,");
		attrs.append("rank,");
		attrs.append("organizations,");
		attrs.append("powers_or_abilities,");
		attrs.append("appears_in_these_fictional_universes,");
		attrs.append("children,");
		attrs.append("character_created_by,");
		attrs.append("quotations,");
		attrs.append("ethnicity,");
		attrs.append("education,");
		attrs.append("has_possessed,");
		attrs.append("medical_conditions,");
		attrs.append("date_of_birth,");
		attrs.append("height,");
		attrs.append("weight,");
		attrs.append("places_lived");

		vals = new StringBuffer();

		for (int i = 0; i < 22; i++) {
			vals.append("?,");
		}
		vals.append("?");

		return ("INSERT INTO " + TableType.CHARACTERS.getStrRep() + "(" + attrs.toString() + ") VALUES (" + vals.toString() + ")");
	}

	/**
	 * prepare a base character update statement
	 * 
	 * @return a string containing UPDATE to all the character attributes with
	 *         the VALUE ?
	 */
	private String prepareStatmentForCharacterUpdate() {

		StringBuffer attrs = new StringBuffer();

		attrs = new StringBuffer();

		attrs.append("character_name = ?, ");
		attrs.append("free_base_id = ?, ");
		attrs.append("based_on = ?, ");
		attrs.append("place_of_birth = ?, ");
		attrs.append("gender = ?, ");
		attrs.append("species = ?, ");
		attrs.append("parents = ?, ");
		attrs.append("occupation = ?, ");
		attrs.append("rank = ?, ");
		attrs.append("organizations = ?, ");
		attrs.append("powers_or_abilities = ?, ");
		attrs.append("appears_in_these_fictional_universes = ?, ");
		attrs.append("children = ?, ");
		attrs.append("character_created_by = ?, ");
		attrs.append("quotations = ?, ");
		attrs.append("ethnicity = ?, ");
		attrs.append("education = ?, ");
		attrs.append("has_possessed = ?, ");
		attrs.append("medical_conditions = ?, ");
		attrs.append("date_of_birth = ?, ");
		attrs.append("height = ?, ");
		attrs.append("weight = ?, ");
		attrs.append("places_lived = ? ");

		return ("UPDATE " + TableType.CHARACTERS.getStrRep() + " SET " + attrs.toString() + " WHERE character_id = ?");
	}

	/**
	 * add a user to the users table
	 * 
	 * @param user
	 */
	public Integer insertToUserTable(User user) {

		ResultSet rs;
		Integer id = null;
		PreparedStatement pstmt;
		StringBuffer attrs = new StringBuffer();
		StringBuffer vals = new StringBuffer();
		Connection conn = openConn();

		// create and update full statement
		try {
			attrs = new StringBuffer();

			attrs.append("user_name,");
			attrs.append("password_text,");
			attrs.append("first_name,");
			attrs.append("last_name,");
			attrs.append("email,");
			attrs.append("gender,");
			attrs.append("date_of_birth,");
			attrs.append("interested_in");

			vals = new StringBuffer();

			for (int i = 0; i < 7; i++) {
				vals.append("?,");
			}
			vals.append("?");

			pstmt = conn.prepareStatement("INSERT INTO " + TableType.USERS.getStrRep() + "(" + attrs.toString() + ") VALUES (" + vals.toString() + ")", Statement.RETURN_GENERATED_KEYS);

			// append all user's values
			pstmt.setString(1, user.getUserName());
			pstmt.setString(2, user.getPassword());
			pstmt.setString(3, user.getFirstName());
			pstmt.setString(4, user.getLastName());
			pstmt.setString(5, user.getEmail());
			pstmt.setString(6, user.getGender());
			pstmt.setDate(7, user.getDateOfBirth());
			pstmt.setString(8, user.getIntrestedIn());

			pstmt.executeUpdate();
			rs = pstmt.getGeneratedKeys();
			rs.next();
			id = rs.getInt(1);
			pstmt.close();
		} catch (Exception e) {
			logger.error("Error while trying to insert to users table.", e);
		} finally {
			closeConn(conn);
		}
		return id;
	}

	/**
	 * updates user details in users table
	 * 
	 * @param user
	 */
	public void updateToUserTable(User user) {

		PreparedStatement pstmt;
		StringBuffer attrs = new StringBuffer();
		Connection conn = openConn();

		// create and update full statement
		try {
			attrs = new StringBuffer();

			attrs.append("user_name = ?, ");
			attrs.append("password_text = ?, ");
			attrs.append("first_name = ?, ");
			attrs.append("last_name = ?, ");
			attrs.append("email = ?, ");
			attrs.append("gender = ?, ");
			attrs.append("date_of_birth = ?, ");
			attrs.append("interested_in = ?");

			pstmt = conn.prepareStatement("UPDATE " + TableType.USERS.getStrRep() + " SET " + attrs.toString() + " WHERE user_id = ?");

			// append all user's values
			pstmt.setString(1, user.getUserName());
			pstmt.setString(2, user.getPassword());
			pstmt.setString(3, user.getFirstName());
			pstmt.setString(4, user.getLastName());
			pstmt.setString(5, user.getEmail());
			pstmt.setString(6, user.getGender());
			pstmt.setDate(7, user.getDateOfBirth());
			pstmt.setString(8, user.getIntrestedIn());
			pstmt.setInt(9, user.getID());

			pstmt.executeUpdate();
			pstmt.close();
		} catch (Exception e) {
			logger.error("Error while trying to update users table.", e);
		} finally {
			closeConn(conn);
		}
	}

	/**
	 * add a date to the dates table
	 * 
	 * @param legenDate
	 */
	public Integer insertToDatesTable(LegenDate legenDate) {

		ResultSet rs;
		Integer id = null;
		PreparedStatement pstmt;
		StringBuffer attrs = new StringBuffer();
		StringBuffer vals = new StringBuffer();
		Connection conn = openConn();

		// create and update full statement
		try {
			attrs = new StringBuffer();

			attrs.append("user_id,");
			attrs.append("character_id,");
			attrs.append("date_time");

			vals = new StringBuffer();

			for (int i = 0; i < 2; i++) {
				vals.append("?,");
			}
			vals.append("?");

			pstmt = conn.prepareStatement("INSERT INTO " + TableType.DATES.getStrRep() + "(" + attrs.toString() + ") VALUES (" + vals.toString() + ")", Statement.RETURN_GENERATED_KEYS);

			// append all legenDate's values
			pstmt.setInt(1, legenDate.getUserID());
			pstmt.setInt(2, legenDate.getCharacterID());
			pstmt.setDate(3, legenDate.getDate());

			pstmt.executeUpdate();
			rs = pstmt.getGeneratedKeys();
			rs.next();
			id = rs.getInt(1);
			pstmt.close();
		} catch (Exception e) {
			logger.error("Error while trying to insert to dates table.", e);
		} finally {
			closeConn(conn);
		}
		return id;
	}

	/**
	 * add a comment to the comments table
	 * 
	 * @param comment
	 */
	public Integer insertToCommentsTable(Comment comment) {

		ResultSet rs;
		Integer id = null;
		PreparedStatement pstmt;
		StringBuffer attrs = new StringBuffer();
		StringBuffer vals = new StringBuffer();
		Connection conn = openConn();

		// create and update full statement
		try {
			attrs = new StringBuffer();

			attrs.append("user_id,");
			attrs.append("character_id,");
			attrs.append("comment_text");

			vals = new StringBuffer();

			for (int i = 0; i < 2; i++) {
				vals.append("?,");
			}
			vals.append("?");

			pstmt = conn.prepareStatement("INSERT INTO " + TableType.COMMENTS.getStrRep() + "(" + attrs.toString() + ") VALUES (" + vals.toString() + ")", Statement.RETURN_GENERATED_KEYS);

			// append all comment's values
			pstmt.setInt(1, comment.getUserID());
			pstmt.setInt(2, comment.getCharacterID());
			pstmt.setString(3, comment.getComment());

			pstmt.executeUpdate();
			rs = pstmt.getGeneratedKeys();
			rs.next();
			id = rs.getInt(1);
			pstmt.close();
		} catch (Exception e) {
			logger.error("Error while trying to insert to comments table.", e);
		} finally {
			closeConn(conn);
		}
		return id;
	}

	/**
	 * find a user by name in the users table
	 * 
	 * @param username
	 * @return res the user or null if user does'nt exists
	 */
	public User findUserByName(String username) {

		ResultSet rs;
		User res = null;
		Statement stmt;
		Connection conn = openConn();

		try {
			stmt = conn.createStatement();

			rs = stmt.executeQuery("SELECT * FROM " + TableType.USERS.getStrRep() + " WHERE user_name = \"" + username + "\"");
			if (rs.next() != false) {
				res = new User();
				res.setID(rs.getInt(1));
				res.setUserName(rs.getString(2));
				res.setPassword(rs.getString(3));
				res.setFirstName(rs.getString(4));
				res.setLastName(rs.getString(5));
				res.setEmail(rs.getString(6));
				res.setGender(rs.getString(7));
				res.setDateOfBirth(rs.getDate(8));
				res.setIntrestedIn(rs.getString(9));
			}
			stmt.close();
		} catch (SQLException e) {
			logger.error("Error while trying to find user by name.", e);
		} finally {
			closeConn(conn);
		}
		return res;
	}

	/**
	 * find a list of date by user id in the dates table
	 * 
	 * @param userID
	 * @return res a list of dates
	 */
	public List<LegenDate> getLegenDatesByUserID(Integer userID) {
		ResultSet rs;
		Statement stmt;
		LegenDate ld;
		List<LegenDate> res = new ArrayList<LegenDate>();
		Connection conn = openConn();

		StringBuffer st = new StringBuffer();

		st.append("SELECT ");
		st.append(TableType.DATES.getStrRep());
		st.append(".*, ");
		st.append(TableType.CHARACTERS.getStrRep());
		st.append(".character_name FROM ");
		st.append(TableType.DATES.getStrRep());
		st.append(" LEFT JOIN ");
		st.append(TableType.CHARACTERS.getStrRep());
		st.append(" ON ");
		st.append(TableType.DATES.getStrRep());
		st.append(".character_id = ");
		st.append(TableType.CHARACTERS.getStrRep());
		st.append(".character_id ");
		st.append(" WHERE ");
		st.append(TableType.DATES.getStrRep());
		st.append(".user_id = ");
		st.append(userID);
		st.append(" ORDER BY ");
		st.append(TableType.DATES.getStrRep());
		st.append(".date_time");

		try {
			stmt = conn.createStatement();

			rs = stmt.executeQuery(st.toString());
			while (rs.next() != false) {
				ld = new LegenDate();
				ld.setUserID(rs.getInt(2));
				ld.setCharacterID(rs.getInt(3));
				ld.setDate(rs.getDate(4));
				ld.setCharacterName(rs.getString(5));
				res.add(ld);
			}
			stmt.close();
		} catch (SQLException e) {
			logger.error("Error while trying to get LegenDates by user ID.", e);
		} finally {
			closeConn(conn);
		}
		return res;
	}

	/**
	 * find a character by character id in the characters table
	 * 
	 * @param characterID
	 * @return res the character or null if character does'nt exists
	 */
	public LegenCharacter getCharacterByID(Integer characterID) {
		ResultSet rs;
		Statement stmt;
		LegenCharacter res = null;
		Connection conn = openConn();

		try {
			stmt = conn.createStatement();

			rs = stmt.executeQuery("SELECT * FROM " + TableType.CHARACTERS.getStrRep() + " WHERE character_id = " + characterID);
			if (rs.next() != false) {
				res = new LegenCharacter();
				res.setCharacterID(rs.getInt(1));
				res.setCharacterName(rs.getString(2));
				res.setCharacterFBID(rs.getString(3));
				res.setBasedOn(rs.getString(4));
				res.setPlaceOfBirth(rs.getString(5));
				res.setGender(rs.getString(6));
				res.setSpecies(rs.getString(7));
				res.setParents(rs.getString(8));
				res.setOccupation(rs.getString(9));
				res.setRank(rs.getString(10));
				res.setOrganizations(rs.getString(11));
				res.setPowersOrAbilities(rs.getString(12));
				res.setAppearsInTheseFictionalUniverses(rs.getString(13));
				res.setChildren(rs.getString(14));
				res.setCharacterCreatedBy(rs.getString(15));
				res.setQuotations(rs.getString(16));
				res.setEthnicity(rs.getString(17));
				res.setEducation(rs.getString(18));
				res.setHasPossessed(rs.getString(19));
				res.setMedicalConditions(rs.getString(20));
				res.setDateOfBirth(rs.getString(21));
				res.setHeight(rs.getFloat(22));
				res.setWeight(rs.getFloat(23));
				res.setPlacesLived(rs.getString(24));
			}
			stmt.close();
		} catch (SQLException e) {
			logger.error("Error while trying to get character by ID.", e);
		} finally {
			closeConn(conn);
		}
		return res;
	}

	/**
	 * find how many days passed since last update to DB
	 * 
	 * @return res number of days
	 */
	public int getDaysSinceLastFreebaseUpdateDate() {
		ResultSet rs;
		Statement stmt;
		int res = -1;
		Connection conn = openConn();

		try {
			stmt = conn.createStatement();

			rs = stmt.executeQuery("SELECT TIMESTAMPDIFF(day, MAX(date_updated), current_timestamp) FROM " + TableType.FREEBASE_UPDATES.getStrRep());
			if (rs.next() != false)
				res = rs.getInt(1);
			if (rs.wasNull())
				res = -1;
			stmt.close();
		} catch (Exception e) {
			logger.error("Error while trying to get last Freebase update date.", e);
		} finally {
			closeConn(conn);
		}
		return res;
	}

	/**
	 * updates date for last update to DB
	 */
	public void setLastFreebaseUpdateDate() {
		Statement stmt;
		Connection conn = openConn();

		// create and update full statement
		try {
			stmt = conn.createStatement();
			stmt.executeUpdate("INSERT INTO " + TableType.FREEBASE_UPDATES.getStrRep() + "(date_updated) VALUES(now())");
			stmt.close();
		} catch (Exception e) {
			logger.error("Error while trying to set last Freebase update date.", e);
		} finally {
			closeConn(conn);
		}
	}

	/**
	 * gets list of comments for a character by character id
	 * 
	 * @param characterID
	 * @return res a list of comments
	 */
	public List<Comment> getCommentsByCharacterID(Integer characterID) {
		ResultSet rs;
		Statement stmt;
		List<Comment> res = new ArrayList<Comment>();
		Comment lc;
		Connection conn = openConn();

		StringBuffer st = new StringBuffer();

		st.append("SELECT ");
		st.append(TableType.COMMENTS.getStrRep());
		st.append(".*, ");
		st.append(TableType.USERS.getStrRep());
		st.append(".user_name FROM ");
		st.append(TableType.COMMENTS.getStrRep());
		st.append(" LEFT JOIN ");
		st.append(TableType.USERS.getStrRep());
		st.append(" ON ");
		st.append(TableType.COMMENTS.getStrRep());
		st.append(".user_id = ");
		st.append(TableType.USERS.getStrRep());
		st.append(".user_id ");
		st.append(" WHERE ");
		st.append(TableType.COMMENTS.getStrRep());
		st.append(".character_id = ");
		st.append(characterID);

		try {
			stmt = conn.createStatement();

			rs = stmt.executeQuery(st.toString());
			while (rs.next() != false) {
				lc = new Comment();
				lc.setCommentID(rs.getInt(1));
				lc.setUserID(rs.getInt(2));
				lc.setCharacterID(rs.getInt(3));
				lc.setComment(rs.getString(4));
				lc.setUserName(rs.getString(5));
				res.add(lc);
			}
			stmt.close();
		} catch (SQLException e) {
			logger.error("Error while trying to get comments by character ID.", e);
		} finally {
			closeConn(conn);
		}
		return res;
	}

	/**
	 * gets list of characters by character name, gender and species
	 * 
	 * @param characterName
	 * @param gender
	 * @param species
	 * @return res the search results
	 */
	public SearchResults getSearchResults(String characterName, String gender, String species) {

		ResultSet rs;
		Statement stmt;
		LegenCharacterThin lct;
		int counter = 0;
		int resultsLimit = Constants.SEARCH_RESULTS_LIMIT;
		SearchResults res = new SearchResults();
		List<LegenCharacterThin> resTemp = new ArrayList<LegenCharacterThin>();
		Connection conn = openConn();
		StringBuffer st = new StringBuffer();

		st.append("SELECT character_id, character_name, gender, species, appears_in_these_fictional_universes FROM " + TableType.CHARACTERS.getStrRep() + " WHERE 1=1 ");
		if (!characterName.equals("")) {
			st.append(" AND character_name like \"%");
			st.append(characterName);
			st.append("%\"");
		}
		if (!gender.equals("")) {
			st.append(" AND gender = \"");
			st.append(gender);
			st.append("\"");
		}
		if (!species.equals("")) {
			st.append(" AND species like \"%");
			st.append(species);
			st.append("%\"");
		}

		st.append(" ORDER BY ");
		st.append("character_name");
		st.append(" LIMIT ");
		st.append(resultsLimit + 1);

		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(st.toString());

			while ((rs.next() != false) && (counter < resultsLimit + 1)) {
				lct = new LegenCharacterThin();
				lct.setCharacterID(rs.getInt(1));
				lct.setCharacterName(rs.getString(2));
				lct.setGender(rs.getString(3));
				lct.setSpecies(rs.getString(4));
				lct.setAppearsInTheseFictionalUniverses(rs.getString(5));
				resTemp.add(lct);
				counter++;
			}
			res.setCharacterDetails(resTemp);
			if (counter == resultsLimit + 1)
				res.setTooMany(true);
			else
				res.setTooMany(false);
			stmt.close();
		} catch (SQLException e) {
			logger.error("Error while trying to find a character : " + st.toString(), e);
		} finally {
			closeConn(conn);
		}
		return res;
	}

	/**
	 * prepare a base character review insert statement
	 * 
	 * @return a string containing INSERT to all the character attributes with
	 *         the VALUE ?
	 */
	private String prepareStatmentForCharacterReview() {

		StringBuffer attrs = new StringBuffer();
		StringBuffer vals = new StringBuffer();

		attrs.append("character_id,");
		attrs.append("affectionate_or_cold,");
		attrs.append("cheerful_or_grumpy,");
		attrs.append("considerate_or_inconsiderate,");
		attrs.append("courteous_or_rude,");
		attrs.append("generous_or_stingy,");
		attrs.append("friendly_or_hostile,");
		attrs.append("honest_or_dishonest,");
		attrs.append("humble_or_arrogant,");
		attrs.append("kind_or_cruel,");
		attrs.append("mature_or_immature,");
		attrs.append("self_confident_or_insecure,");
		attrs.append("selfless_or_selfish,");
		attrs.append("sensitive_or_Insensitive,");
		attrs.append("serious_or_silly,");
		attrs.append("clean_or_dirty,");
		attrs.append("good_or_evil,");
		attrs.append("good_looking_or_ugly,");
		attrs.append("graceful_or_clumsy,");
		attrs.append("intelligent_or_stupid,");
		attrs.append("wealthy_or_poor");

		for (int i = 0; i < 20; i++) {
			vals.append("?,");
		}
		vals.append("?");

		return ("INSERT INTO " + TableType.REVIEWS.getStrRep() + "(" + attrs.toString() + ") VALUES (" + vals.toString() + ")");
	}

	/**
	 * add a new review for a character
	 * 
	 * @param review
	 */
	public Integer insertNewCharacterReview(Review review) {

		ResultSet rs;
		Integer id = null;
		PreparedStatement pstmt;
		Connection conn = openConn();

		// create and update full statement
		try {

			pstmt = conn.prepareStatement(prepareStatmentForCharacterReview(), Statement.RETURN_GENERATED_KEYS);

			// append all review's values
			pstmt.setInt(1, review.getCharacterID());
			pstmt.setInt(2, review.getAffectionateORcold());
			pstmt.setInt(3, review.getCheerfulORgrumpy());
			pstmt.setInt(4, review.getConsiderateORinconsiderate());
			pstmt.setInt(5, review.getCourteousORrude());
			pstmt.setInt(6, review.getGenerousORstingy());
			pstmt.setInt(7, review.getFriendlyORhostile());
			pstmt.setInt(8, review.getHonestORdishonest());
			pstmt.setInt(9, review.getHumbleORarrogant());
			pstmt.setInt(10, review.getKindORcruel());
			pstmt.setInt(11, review.getMatureORimmature());
			pstmt.setInt(12, review.getSelfConfidentORinsecure());
			pstmt.setInt(13, review.getSelflessORselfish());
			pstmt.setInt(14, review.getSensitiveORInsensitive());
			pstmt.setInt(15, review.getSeriousORsilly());
			pstmt.setInt(16, review.getCleanORdirty());
			pstmt.setInt(17, review.getGoodORevil());
			pstmt.setInt(18, review.getGoodLookingORugly());
			pstmt.setInt(19, review.getGracefulORclumsy());
			pstmt.setInt(20, review.getIntelligentORstupid());
			pstmt.setInt(21, review.getWealthyORpoor());

			pstmt.executeUpdate();
			rs = pstmt.getGeneratedKeys();
			rs.next();
			id = rs.getInt(1);
			pstmt.close();
		} catch (Exception e) {
			logger.error("Error while trying to insert to reviews table.", e);
		} finally {
			closeConn(conn);
		}
		return id;
	}

	/**
	 * gets a review result for a character by character id
	 * 
	 * @param characterID
	 * @return rv the review for the character
	 */
	public Review getCharacterReview(Integer characterID) {

		ResultSet rs;
		Statement stmt;
		Review rv = new Review();
		Connection conn = openConn();

		StringBuffer attrs = new StringBuffer();

		attrs.append("AVG(affectionate_or_cold),");
		attrs.append("AVG(cheerful_or_grumpy),");
		attrs.append("AVG(considerate_or_inconsiderate),");
		attrs.append("AVG(courteous_or_rude),");
		attrs.append("AVG(generous_or_stingy),");
		attrs.append("AVG(friendly_or_hostile),");
		attrs.append("AVG(honest_or_dishonest),");
		attrs.append("AVG(humble_or_arrogant),");
		attrs.append("AVG(kind_or_cruel),");
		attrs.append("AVG(mature_or_immature),");
		attrs.append("AVG(self_confident_or_insecure),");
		attrs.append("AVG(selfless_or_selfish),");
		attrs.append("AVG(sensitive_or_Insensitive),");
		attrs.append("AVG(serious_or_silly),");
		attrs.append("AVG(clean_or_dirty),");
		attrs.append("AVG(good_or_evil),");
		attrs.append("AVG(good_looking_or_ugly),");
		attrs.append("AVG(graceful_or_clumsy),");
		attrs.append("AVG(intelligent_or_stupid),");
		attrs.append("AVG(wealthy_or_poor)");

		try {
			stmt = conn.createStatement();

			rs = stmt.executeQuery("SELECT " + attrs + " FROM " + TableType.REVIEWS.getStrRep() + " WHERE character_id = " + characterID);
			if (rs.next() != false) {
				rv.setCharacterID(characterID);
				rv.setAffectionateORcold(rs.getInt(1));
				rv.setCheerfulORgrumpy(rs.getInt(2));
				rv.setConsiderateORinconsiderate(rs.getInt(3));
				rv.setCourteousORrude(rs.getInt(4));
				rv.setGenerousORstingy(rs.getInt(5));
				rv.setFriendlyORhostile(rs.getInt(6));
				rv.setHonestORdishonest(rs.getInt(7));
				rv.setHumbleORarrogant(rs.getInt(8));
				rv.setKindORcruel(rs.getInt(9));
				rv.setMatureORimmature(rs.getInt(10));
				rv.setSelfConfidentORinsecure(rs.getInt(11));
				rv.setSelflessORselfish(rs.getInt(12));
				rv.setSensitiveORInsensitive(rs.getInt(13));
				rv.setSeriousORsilly(rs.getInt(14));
				rv.setCleanORdirty(rs.getInt(15));
				rv.setGoodORevil(rs.getInt(16));
				rv.setGoodLookingORugly(rs.getInt(17));
				rv.setGracefulORclumsy(rs.getInt(18));
				rv.setIntelligentORstupid(rs.getInt(19));
				rv.setWealthyORpoor(rs.getInt(20));
			}
			stmt.close();
		} catch (SQLException e) {
			logger.error("Error while trying to get review for character ID.", e);
		} finally {
			closeConn(conn);
		}
		return rv;
	}

	/**
	 * finds the top or bottom characters by score
	 * 
	 * @param TorB
	 * @return res a list of top or bottom characters
	 */
	private List<LegenCharacterThin> getTopOrBottemTenCharactersFromDB(String TorB) {

		ResultSet rs;
		Statement stmt;
		LegenCharacterThin lct;
		List<LegenCharacterThin> res = new ArrayList<LegenCharacterThin>(10);
		Connection conn = openConn();
		StringBuffer st = new StringBuffer();

		st.append("SELECT DISTINCT ");
		st.append(TableType.CHARACTERS.getStrRep());
		st.append(".character_id, ");
		st.append(TableType.CHARACTERS.getStrRep());
		st.append(".character_name, ");
		st.append(TableType.CHARACTERS.getStrRep());
		st.append(".gender, ");
		st.append(TableType.CHARACTERS.getStrRep());
		st.append(".species, ");
		st.append(TableType.CHARACTERS.getStrRep());
		st.append(".appears_in_these_fictional_universes, ");
		st.append(TableType.CHARTS.getStrRep());
		st.append(".score FROM ");
		st.append(TableType.CHARACTERS.getStrRep());
		st.append(" RIGHT JOIN ");
		st.append(TableType.CHARTS.getStrRep());
		st.append(" ON ");
		st.append(TableType.CHARACTERS.getStrRep());
		st.append(".character_id = ");
		st.append(TableType.CHARTS.getStrRep());
		st.append(".character_id ");
		st.append(" ORDER BY ");
		st.append(TableType.CHARTS.getStrRep());
		st.append(".score ");
		st.append(TorB);
		st.append(" LIMIT 10");

		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(st.toString());

			while (rs.next() != false) {
				lct = new LegenCharacterThin();
				lct.setCharacterID(rs.getInt(1));
				lct.setCharacterName(rs.getString(2));
				lct.setGender(rs.getString(3));
				lct.setSpecies(rs.getString(4));
				lct.setAppearsInTheseFictionalUniverses(rs.getString(5));
				res.add(lct);
			}
			stmt.close();
		} catch (SQLException e) {
			logger.error("Error while trying to find a character : " + st.toString(), e);
		} finally {
			closeConn(conn);
		}
		return res;
	}

	/**
	 * gets the top ten characters
	 * 
	 * @return list of top ten characters
	 */
	public List<LegenCharacterThin> getTopTenCharactersFromDB() {
		return getTopOrBottemTenCharactersFromDB("DESC");
	}

	/**
	 * gets the bottom ten characters
	 * 
	 * @return list of bottom ten characters
	 */
	public List<LegenCharacterThin> getBottomTenCharactersFromDB() {
		return getTopOrBottemTenCharactersFromDB("ASC");
	}

	/**
	 * finds the top ten reviewed characters
	 * 
	 * @return list of top ten reviewed characters
	 */
	public List<LegenCharacterThin> getTopTenReviewedCharactersFromDB() {

		ResultSet rs;
		Statement stmt;
		LegenCharacterThin lct;
		List<LegenCharacterThin> res = new ArrayList<LegenCharacterThin>(10);
		Connection conn = openConn();
		StringBuffer st = new StringBuffer();

		st.append("SELECT DISTINCT ");
		st.append(TableType.CHARACTERS.getStrRep());
		st.append(".character_id, ");
		st.append(TableType.CHARACTERS.getStrRep());
		st.append(".character_name, ");
		st.append(TableType.CHARACTERS.getStrRep());
		st.append(".gender, ");
		st.append(TableType.CHARACTERS.getStrRep());
		st.append(".species, ");
		st.append(TableType.CHARACTERS.getStrRep());
		st.append(".appears_in_these_fictional_universes, ");
		st.append(TableType.CHARTS.getStrRep());
		st.append(".reviews_amount FROM ");
		st.append(TableType.CHARACTERS.getStrRep());
		st.append(" RIGHT JOIN ");
		st.append(TableType.CHARTS.getStrRep());
		st.append(" ON ");
		st.append(TableType.CHARACTERS.getStrRep());
		st.append(".character_id = ");
		st.append(TableType.CHARTS.getStrRep());
		st.append(".character_id ");
		st.append(" ORDER BY ");
		st.append(TableType.CHARTS.getStrRep());
		st.append(".reviews_amount ");
		st.append("DESC");
		st.append(" LIMIT 10");

		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(st.toString());

			while (rs.next() != false) {
				lct = new LegenCharacterThin();
				lct.setCharacterID(rs.getInt(1));
				lct.setCharacterName(rs.getString(2));
				lct.setGender(rs.getString(3));
				lct.setSpecies(rs.getString(4));
				lct.setAppearsInTheseFictionalUniverses(rs.getString(5));
				res.add(lct);
			}
			stmt.close();
		} catch (SQLException e) {
			logger.error("Error while trying to find a character : " + st.toString(), e);
		} finally {
			closeConn(conn);
		}
		closeConn(conn);

		return res;
	}

	/**
	 * checks if date is already set in date table for a character
	 * 
	 * @param legenDate
	 * @return res TURE if date is already taken and FALSE otherwise
	 */
	public boolean checkIfDateIsTaken(LegenDate legenDate) {

		ResultSet rs;
		PreparedStatement stmt;
		boolean res = false;
		Connection conn = openConn();

		try {
			stmt = conn.prepareStatement("SELECT * FROM " + TableType.DATES.getStrRep() + " WHERE character_id=? AND date_time=?");
			stmt.setInt(1, legenDate.getCharacterID());
			stmt.setDate(2, legenDate.getDate());

			rs = stmt.executeQuery();
			res = rs.next();
			stmt.close();
		} catch (Exception e) {
			logger.error("Error while trying to set the date.", e);
		} finally {
			closeConn(conn);
		}
		return res;
	}

	/**
	 * add a favorite to the favorites table
	 * 
	 * @param userID
	 * @param characterID
	 */
	public void insertToFavoritesTable(Integer userID, Integer characterID) {

		PreparedStatement pstmt;
		StringBuffer attrs = new StringBuffer();
		StringBuffer vals = new StringBuffer();
		Connection conn = openConn();

		// create and update full statement
		try {
			attrs = new StringBuffer();

			attrs.append("user_id,");
			attrs.append("character_id");

			vals = new StringBuffer();
			vals.append("?,");
			vals.append("?");

			pstmt = conn.prepareStatement("INSERT INTO " + TableType.FAVORITES.getStrRep() + "(" + attrs.toString() + ") VALUES (" + vals.toString() + ")");

			// append all favorite's values
			pstmt.setInt(1, userID);
			pstmt.setInt(2, characterID);

			pstmt.executeUpdate();
			pstmt.close();
		} catch (Exception e) {
			logger.error("Error while trying to insert to favorites table.", e);
		} finally {
			closeConn(conn);
		}
	}

	/**
	 * remove a favorite from the favorites table
	 * 
	 * @param userID
	 * @param characterID
	 */
	public void removeFromFavoritesTable(Integer userID, Integer characterID) {

		PreparedStatement pstmt;
		StringBuffer attrs = new StringBuffer();
		StringBuffer vals = new StringBuffer();
		Connection conn = openConn();

		// create and update full statement
		try {
			attrs = new StringBuffer();

			attrs.append("user_id,");
			attrs.append("character_id,");

			vals = new StringBuffer();
			vals.append("?,");
			vals.append("?");

			pstmt = conn.prepareStatement("DELETE FROM " + TableType.FAVORITES.getStrRep() + " WHERE user_id=? AND character_id=?");

			// append all favorite's values
			pstmt.setInt(1, userID);
			pstmt.setInt(2, characterID);

			pstmt.executeUpdate();
			pstmt.close();
		} catch (Exception e) {
			logger.error("Error while trying to remove from favorites table.", e);
		} finally {
			closeConn(conn);
		}
	}

	/**
	 * get all favorite characters by user id
	 * 
	 * @param userID
	 * @return res a list of favorite characters
	 */
	public List<LegenCharacterThin> getUserFavorites(Integer userID) {

		ResultSet rs;
		Statement stmt;
		LegenCharacterThin lct;
		List<LegenCharacterThin> res = new ArrayList<LegenCharacterThin>();
		Connection conn = openConn();

		StringBuffer st = new StringBuffer();

		st.append("SELECT DISTINCT ");
		st.append(TableType.CHARACTERS.getStrRep());
		st.append(".character_id, ");
		st.append(TableType.CHARACTERS.getStrRep());
		st.append(".character_name, ");
		st.append(TableType.CHARACTERS.getStrRep());
		st.append(".gender, ");
		st.append(TableType.CHARACTERS.getStrRep());
		st.append(".species, ");
		st.append(TableType.CHARACTERS.getStrRep());
		st.append(".appears_in_these_fictional_universes FROM ");
		st.append(TableType.CHARACTERS.getStrRep());
		st.append(" RIGHT JOIN ");
		st.append(TableType.FAVORITES.getStrRep());
		st.append(" ON ");
		st.append(TableType.CHARACTERS.getStrRep());
		st.append(".character_id = ");
		st.append(TableType.FAVORITES.getStrRep());
		st.append(".character_id ");
		st.append(" WHERE ");
		st.append(TableType.FAVORITES.getStrRep());
		st.append(".user_id = ");
		st.append(userID);

		try {
			stmt = conn.createStatement();

			rs = stmt.executeQuery(st.toString());
			while (rs.next() != false) {
				lct = new LegenCharacterThin();
				lct.setCharacterID(rs.getInt(1));
				lct.setCharacterName(rs.getString(2));
				lct.setGender(rs.getString(3));
				lct.setSpecies(rs.getString(4));
				lct.setAppearsInTheseFictionalUniverses(rs.getString(5));
				res.add(lct);
			}
			stmt.close();
		} catch (SQLException e) {
			logger.error("Error while trying to get user's favorites list ", e);
		} finally {
			closeConn(conn);
		}
		return res;
	}
}